package analyse;

import org.my.stock.util.MyUtil;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TypeFund1 {

    public static void update_all_type() throws PropertyVetoException, SQLException, ClassNotFoundException {
        update_all_type("type_fund", "2025-04-25", "2025-04-29", 0);
        update_all_type("type_fund_5", "2025-04-23", "2025-04-29", 0);
        update_all_type("type_fund_10", "2025-04-17", "2025-04-29", 0);
    }

    public static void update_all_type(String table_name, String begin, String end, int start) throws SQLException, ClassNotFoundException, PropertyVetoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int count = 0;
        int mynum = 20;
        int limit = mynum + start;

        try {

            conn = MyUtil.getConnection();
            stmt = conn.prepareStatement("select name from stock_type limit "+ limit +", " + mynum);

            // 5. 执行查询并获取结果集
            rs = stmt.executeQuery();

            while (rs.next()) {
                // 获取类型的名字
                String name = rs.getString(1);
                // 获取股票编号
                List<Double> symbol = getSymbol(name, begin, end);
                if (!symbol.isEmpty() && symbol.size() > 0) {
                    if (isExist(table_name, name)) {
                        MyUtil.execute("update "+ table_name +" set zhangdie = '"+ symbol.get(0) +"', main_fund = '"+ symbol.get(1) +"', main_fund_bf = '"+ symbol.get(2) +"' where name = '" + name + "'");
                    } else {
                        MyUtil.execute("insert into "+ table_name +" (name, zhangdie, main_fund, main_fund_bf) values ('" + name + "', " + symbol.get(0) + ", " + symbol.get(1) + ", " + symbol.get(2) + ")");
                    }
                }
                count++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (count == mynum) {
            System.out.println("已经更新了 "+ mynum +" 个板块");
            update_all_type(table_name, begin, end, limit);
        }
    }

    public static boolean isExist(String table_name, String name) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = MyUtil.getConnection();
            stmt = conn.prepareStatement("SELECT name FROM "+ table_name +" WHERE name = '"+ name +"'");
            rs = stmt.executeQuery();
            while (rs.next()) {
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return flag;
    }

    public static List<Double> getFund(String symbol, String begin, String end) throws SQLException, ClassNotFoundException, PropertyVetoException {
        List<Double> lst = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = MyUtil.getConnection();
            stmt = conn.prepareStatement("SELECT AVG(m.zhangdie), AVG(m.main_fund), AVG(m.main_fund_bf) FROM df_stock_date m WHERE m.SYMBOL = '"+ symbol +"' AND m.STOCK_DATE BETWEEN '"+ begin +"' AND '"+ end +"'");
            rs = stmt.executeQuery();
            while (rs.next()) {
                lst.add(rs.getDouble(1));
                lst.add(rs.getDouble(2));
                lst.add(rs.getDouble(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return lst;
    }

    public static List<Double> getSymbol(String type_name, String begin, String end) throws SQLException, ClassNotFoundException, PropertyVetoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Double> mylst = new ArrayList<>();

        try {
            conn = MyUtil.getConnection();
            stmt = conn.prepareStatement("SELECT m.SYMBOL FROM public.type_stock m where m.type_name = '"+ type_name +"'");
            rs = stmt.executeQuery();

            String name = null;
            Double zhangdie = 0D;
            Double main_fund = 0D;
            Double main_fund_bf = 0D;
            int count = 0;

            while (rs.next()) {
                // 获取股票编号
                name = rs.getString(1);
                List<Double> lst = getFund(name, begin, end);
                if (!lst.isEmpty() && lst.size() > 0) {
                    zhangdie += lst.get(0);
                    main_fund += lst.get(1);
                    main_fund_bf += lst.get(2);
                    count++;
                }
            }

            if (count > 0) {
                mylst.add(zhangdie / count);
                mylst.add(main_fund / count);
                mylst.add(main_fund_bf / count);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭资源（反向顺序）
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return mylst;
    }
}
